package com.zyp.messageboard.dao.impl;

import com.zyp.messageboard.dao.MessageDao;
import com.zyp.messageboard.dao.UserDao;
import com.zyp.messageboard.entity.Message;
import com.zyp.messageboard.util.DatabaseUtil;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.List;

public class MessageDaoImpl implements MessageDao {

    @Override
    public Message findById(Integer id) {
        String sql = "SELECT * FROM messages WHERE id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return mapToMessage(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Message> findByUserId(Integer userId) {
        String sql = "SELECT * FROM messages WHERE user_id = ?";
        List<Message> messages = new ArrayList<>();
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                messages.add(mapToMessage(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    @Override
    public List<Message> findAll() {
        String sql = "SELECT * FROM messages";
        List<Message> messages = new ArrayList<>();
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                messages.add(mapToMessage(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messages;
    }

    @Override
    public void save(Message message) {
        String sql = "INSERT INTO messages (title, content, created_at, user_id) VALUES (?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, message.getTitle());
            pstmt.setString(2, message.getContent());
            pstmt.setTimestamp(3, Timestamp.valueOf(message.getCreatedAt()));
            pstmt.setInt(4, message.getUser().getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Message message) {
        String sql = "UPDATE messages SET title = ?, content = ?, created_at = ?, user_id = ? WHERE id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, message.getTitle());
            pstmt.setString(2, message.getContent());
            pstmt.setTimestamp(3, Timestamp.valueOf(message.getCreatedAt()));
            pstmt.setInt(4, message.getUser().getId());
            pstmt.setInt(5, message.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(Integer id) {
        String sql = "DELETE FROM messages WHERE id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Message mapToMessage(ResultSet rs) throws SQLException {
        Message message = new Message();
        message.setId(rs.getInt("id"));
        message.setTitle(rs.getString("title"));
        message.setContent(rs.getString("content"));

        // Convert Timestamp to LocalDateTime
        Timestamp timestamp = rs.getTimestamp("created_at");
        if (timestamp != null) {
            message.setCreatedAt(timestamp.toLocalDateTime());
        } else {
            message.setCreatedAt(null);
        }

        // Assuming you have a method to fetch User by ID
        Integer userId = rs.getInt("user_id");
        UserDao userDao = new UserDaoImpl();
        message.setUser(userDao.findById(userId));

        return message;
    }
}
